<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of LoanApplication
 *
 * @author andres
 */
class Emms_LoanApplication 
{
    private $_application;
    private $_loans;
    
    public function __construct($master, $loan = null) 
    {
        if($master) {
            $this->_constructFromMaster($master);
        } elseif($loan) {
            $this->_constructFromLoan($loan);
        } else {
            throw new Exception('Invalid request');
        }
        $this->_loadLoans();
    }
    
    private function _loadLoans()
    {
        $this->_loans = array();
        $loans = new Loans(array('name' => 'loans'));
        $select = $loans->select();
        $select->where('LoanMasterId = ?', $this->_application->LoanMasterId);
        $rows = $loans->fetchAll($select);
        foreach($rows as $loan) {
            $this->_loans[$loan->LoanId] = $loan;
        }
    }
    
    private function _constructFromMaster($id)
    {
        $masters = new LoanMasters(array('name' => 'loan_masters'));
        $this->_application = $masters->find($id)->current();
        if(!$this->_application) throw new Exception('Invalid request');
    }
    
    private function _constructFromLoan($id)
    {
        $loans = new Loans(array('name' => 'loans'));
        $loan = $loans->find($id)->current();
        if(!$loan) throw new Exception('Invalid request');
        $this->_constructFromMaster($loan->LoanMasterId);
    }
    
    public function isSubmitReady()
    {
        $status = new Emms_LoanStatus($this->_application->Status);
        if(!$status->isSubmitOkay()) return false;
        
        $results = Zend_Registry::get('db')
            ->fetchAll("
                select 
                    if(p.Business='Yes',if(l.BusinessId>0,1,0),1) Business,
                    if(count(sc.SurveyId)>0,1,0) Survey 
                from 
                    loans l
                left join
                    loan_masters lm on lm.LoanMasterId = l.LoanMasterId
                left join
                    programs p on p.ProgramId = lm.ProgramId
                left join
                    surveys_completed sc on sc.LoanId = l.LoanId 
                where
                    l.LoanMasterId = ? 
                group by
                    l.LoanId
                having 
                    Business = 0 or Survey = 0", $this->_application->LoanMasterId);
         if (count($results) > 0) return false;
         return true;
    }
    
    public function surveysCompleted()
    {       
        return Zend_Registry::get('db')
            ->fetchPairs("select 
                         sc.LoanId,1 
                     from 
                         loan_masters lm
                     left join
                         loans l on l.LoanMasterId = lm.LoanMasterId
                     left join
                         surveys_completed sc on sc.LoanId = l.LoanId 
                     where
                         lm.LoanMasterId = ? and sc.LoanId is not null
                     group by
                         sc.LoanId", $this->_application->LoanMasterId);
    }
    
    private function _changeLoanMasterStatus($status)
    {
        $this->_application->Status = $status;
        $this->_application->LastEditDate = date('Y-m-d H:i:s');
        $this->_application->LastEditUserId = Zend_Auth::getInstance()->getIdentity()->UserId;
        $this->_application->save();
    }
    
    private function _changeLoanStatus($status,$comments="") 
    { 
        $date = date('Y-m-d H:i:s');
        $userId = Zend_Auth::getInstance()->getIdentity()->UserId;
        $sql1 = sprintf('
            insert into loan_status_changes 
                (LoanId, PreviousStatus, Status, Date, Datetime, UserId, Comments)
            select LoanId,Status,"%s","%s","%s","%s","%s" from loans 
            where LoanMasterId = %s', $status,$date,$date,$userId,$comments,
                $this->_application->LoanMasterId);
        Zend_Registry::get('db')->query($sql1);
        $sql2 = sprintf('
            update loans set
                Status = "%s", LastEditDate = "%s", LastEditUserId = "%s"
            where LoanMasterId = %s', $status,$date,$userId,
                $this->_application->LoanMasterId);
        Zend_Registry::get('db')->query($sql2);
    }


    public function submit($data)
    {
        if($this->isSubmitReady()) {
            $this->_changeLoanStatus('Submitted',$data['Comments']);
            $this->_changeLoanMasterStatus('Submitted');
        } else {
            throw new Exception('Invalid request');
        }
    }

    public function reject($data)
    {
        $status = new Emms_LoanStatus($this->_application->Status);
        if($status->isRejectOkay()) {
            $this->_changeLoanStatus('Rejected',$data['Comments']);
            $this->_changeLoanMasterStatus('Rejected');
        } else {
            throw new Exception('Invalid request');
        }
    }

    public function withdraw($data)
    {
        $status = new Emms_LoanStatus($this->_application->Status);
        if($status->isWithdrawOkay()) {
            $this->_changeLoanStatus('Withdrawn',$data['Comments']);
            $this->_changeLoanMasterStatus('Withdrawn');
        } else {
            throw new Exception('Invalid request');
        }
    }

    public function review($data)
    {
        $status = new Emms_LoanStatus($this->_application->Status);
        if($status->isReviewOkay()) {
            $this->_changeLoanStatus('Reviewed',$data['Comments']);
            $this->_changeLoanMasterStatus('Reviewed');
        } else {
            throw new Exception('Invalid request');
        }
    }

    public function approve($data)
    {
        $status = new Emms_LoanStatus($this->_application->Status);
        if($status->isApproveOkay()) {
            $this->_changeLoanStatus('Approved',$data['Comments']);
            $this->_changeLoanMasterStatus('Approved');
        } else {
            throw new Exception('Invalid request');
        }
    }

    public function execute($data)
    {
        $status = new Emms_LoanStatus($this->_application->Status);
        if($status->isExecuteOkay()) {
            $this->_changeLoanStatus('Executed',$data['Comments']);
            $this->_changeLoanMasterStatus('Executed');
            $this->_application->CheckNumber = $data['CheckNumber'];
            $this->_application->FundId = $data['FundId'];
            $this->_application->save();
        } else {
            throw new Exception('Invalid request');
        }
    }

    public function disburse($data)
    {
        $status = new Emms_LoanStatus($this->_application->Status);
        if($status->isDisburseOkay()) {
            $this->_changeLoanStatus('Disbursed',$data['Comments']);
            //$this->_changeLoanMasterStatus('Disbursed');
            
            $loan = current($this->_loans); //echo '<pre>';print_r($data + $loan->toArray());exit;
            $l = new Emms_Loan($data + $loan->toArray() );
            
            $this->_application->Status = 'Disbursed';
            $this->_application->DeliveryDate = $data['DeliveryDate'];
            $this->_application->PrincipalGraceEndDate = $l->getPrincipalGraceEndDate();
            $this->_application->ExpectedFirstPaymentDate = $data['ExpectedFirstPaymentDate'];
            $this->_application->ExpectedCancelDate = $l->getTentativeCancelDate();
            $this->_application->LastEditDate = date('Y-m-d H:i:s');
            $this->_application->LastEditUserId = Zend_Auth::getInstance()->getIdentity()->UserId;
            $this->_application->save();
            
            $sql = sprintf('
                insert into active_loans 
                    (
                        LoanId, 
                        BalancePrincipal, 
                        BalanceLoanInsurance, 
                        BalanceHealthInsurance, 
                        BalanceAdministrativeFee, 
                        PreviousPaymentDate, 
                        NextPaymentDate,
                        ExpectedPaymentAmount, 
                        CurrentDate, 
                        CurrentDelay, 
                        CurrentPenalties  
                    )
                select 
                    l.LoanId, 
                    l.Principal BalancePrincipal, 
                    l.LoanInsuranceMnt BalanceLoanInsurance, 
                    l.HealthInsuranceMnt BalanceHealthInsurance, 
                    l.AdministrativeFeeMnt BalanceAdministrativeFee, 
                    lm.DeliveryDate PreviousPaymentDate, 
                    lm.ExpectedFirstPaymentDate NextPaymentDate, 
                    l.PaymentMnt ExpectedPaymentAmount, 
                    curdate() CurrentDate, 
                    0 CurrentDelay, 
                    0.00 CurrentPenalties  
                  from 
                    loans l, loan_masters lm
                  where
                    l.LoanMasterId = %s and
                    lm.LoanMasterId = l.LoanMasterId', $this->_application->LoanMasterId);
            Zend_Registry::get('db')->query($sql);
        } else {
            throw new Exception('Invalid request');
        }
    }

    public function delete($id)    
    {
        if(!isset($this->_loans[$id])) throw new Exception('Invalid request');
        
        $appStatus = new Emms_LoanStatus($this->_application->Status);
        if(!$appStatus->isDeleteOkay()) throw new Exception('Invalid request');
        
        $loanStatus = new Emms_LoanStatus($this->_loans[$id]->Status);
        if(!$loanStatus->isDeleteOkay()) throw new Exception('Invalid request');
        
        /*
         * delete records in loan_status_changes table
         */
        $status_changes = new LoanStatusChanges(array('name' => 'loan_status_changes'));
        $status_changes->delete($status_changes->getAdapter()->quoteInto('LoanId = ?', $id));
        
        /*
         * delete records in surveys_completed table
         */
        $surveys_completed = new SurveysCompleted(array('name' => 'surveys_completed'));
        $surveys_completed->delete($surveys_completed->getAdapter()->quoteInto('LoanId = ?', $id));
        
        /*
         * delete record in loans table
         */
        $this->_loans[$id]->delete();
        $this->_loadLoans();
                
        /*
         * delete record in loan_masters table if no loans are left
         */
        if(count($this->_loans) == 0) {
            $this->_application->delete();
            return 0;
        } 
        
        /*
         * update record in loan_masters table
         */
        $this->sync();
        return $this->_application->LoanMasterId;
    }
    
    public function sync() 
    {
        $this->_application->Amount = 0;
        foreach($this->_loans as $loan)
            $this->_application->Amount += $loan->Principal;
        $this->_application->LastEditDate = date('Y-m-d H:i:s');
        $this->_application->LastEditUserId = Zend_Auth::getInstance()->getIdentity()->UserId;
        $this->_application->save();
    }
    
    public function isChangeDatesOkay()
    {
        $status = new Emms_LoanStatus($this->_application->Status);
        if(!$status->isChangeDatesOkay()) return false;
        return true;
    }
    
    public function changeDates($data)
    {
        if(!$this->isChangeDatesOkay()) throw new Exception('Invalid request');
        foreach($this->_loans as $loan) {
            $l = new Emms_Loan($data + $loan->toArray());
            $loan->PaymentMnt = $l->getPmt();
            $loan->save();
        } 
        $this->_application->TentativeDeliveryDate = $data['TentativeDeliveryDate'];
        $this->_application->TentativePrincipalGraceEndDate = $l->getPrincipalGraceEndDate();
        $this->_application->TentativeFirstPaymentDate = $data['TentativeFirstPaymentDate'];
        $this->_application->TentativeCancelDate = $l->getTentativeCancelDate();
        $this->_application->LastEditDate = $data['LastEditDate'];
        $this->_application->LastEditUserId = $data['LastEditUserId'];
        $this->_application->save();
    }
    
    public function getLoanMasterId()
    {
        return $this->_application->LoanMasterId;
    }
    
    public function getMaster()
    {
        return $this->_application;
    }
    
    public function getLoans()
    {
        return $this->_loans;
    }    
    
    public function getDonations()
    {
        $donations = new DonationAllocations(array('name' => 'donation_allocations'));
        $select = $donations->select();
        $select->where('LoanMasterId = ?', $this->_application->LoanMasterId);
        return $donations->fetchAll($select);
    }
}

?>
